﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;

namespace To4WebService
{
    public class UserDao
    {
        public static string con = "server=localhost;user=root;password=;database=gametest;sslmode=none;";
        public static int LoginCheck(string username, string password)
        {
            int number = 0;
            //1,构建mysql连接对象 
            MySqlConnection conn = new MySqlConnection(con);
            try
            {
                //2,打开数据库连接
                conn.Open();
                string sql = "SELECT COUNT(id) FROM gametest.tb_users WHERE username='" + username + "' AND password='" + password + "'";
                //3，创建SQL语句
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //4,执行SQL语句

                object result = cmd.ExecuteScalar();
                if (result != null)
                {
                    number = Convert.ToInt32(result);
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine("数据库访问异常。。。");
                Debug.WriteLine("====================");
                Debug.WriteLine(ex.ToString());
                Debug.WriteLine("====================");
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }

            }
            return number;
        }

        public static User GetUserByUsername(string username)
        {
            User user = new User();
            MySqlConnection conn = new MySqlConnection(con);

            try
            {
                conn.Open();
                //模糊查询
                string sql = "SELECT * FROM gametest.tb_users WHERE username = @username";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //安全的参数(占位符)
                cmd.Parameters.AddWithValue("@username", username);
                MySqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                { 
                    user.Id = Convert.ToInt64(rdr["id"]);
                    user.Username = Convert.ToString(rdr["username"]);
                    user.Password = Convert.ToString(rdr["password"]);
                    user.Nickname = Convert.ToString(rdr["nickname"]);
                    user.Is_Admin = Convert.ToInt16(rdr["is_admin"]) == 1 ? true : false; 
                }
                rdr.Close();
            }
            catch (Exception ex)
            {
                Debug.WriteLine("==============");
                Debug.WriteLine(ex.ToString());
                Debug.WriteLine("==============");
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return user;
        }

        public static List<User> LoadDate()
        {
            List<User> users = new List<User>();
            //1,创建数据库连接
            MySqlConnection conn = new MySqlConnection(con);

            try
            {
                //2,打开数据库连接
                conn.Open();
                //3,创建sql语句
                string sql = "SELECT * FROM gametest.tb_users";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //4,获取数据访问的游标（当前记录）
                MySqlDataReader rdr = cmd.ExecuteReader();
                //5,依次访问数据记录并加入数据集合
                while (rdr.Read())
                {
                    User user = new User();
                    user.Id = Convert.ToInt64(rdr["id"]);
                    user.Username = Convert.ToString(rdr["username"]);
                    user.Password = Convert.ToString(rdr["password"]);
                    user.Nickname = Convert.ToString(rdr["nickname"]);
                    user.Is_Admin = Convert.ToInt16(rdr["is_admin"]) == 1 ? true : false;
                    users.Add(user);
                }
                //6,关闭游标
                rdr.Close();
            }
            catch (Exception ex)
            {
                Debug.WriteLine("==============");
                Debug.WriteLine(ex.ToString());
                Debug.WriteLine("==============");
            }
            finally
            {
                //7,关闭数据库连接
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return users;
        }
        public static List<User> LoadDate(string condition)
        {
            List<User> users = new List<User>();
            MySqlConnection conn = new MySqlConnection(con);

            try
            {
                conn.Open();
                //模糊查询
                string sql = "SELECT * FROM gametest.tb_users WHERE username LIKE @username OR nickname LIKE @nickname";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //安全的参数(占位符)
                cmd.Parameters.AddWithValue("@username", "%" + condition + "%");
                cmd.Parameters.AddWithValue("@nickname", "%" + condition + "%");
                MySqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    User user = new User();
                    user.Id = Convert.ToInt64(rdr["id"]);
                    user.Username = Convert.ToString(rdr["username"]);
                    user.Password = Convert.ToString(rdr["password"]);
                    user.Nickname = Convert.ToString(rdr["nickname"]);
                    user.Is_Admin = Convert.ToInt16(rdr["is_admin"]) == 1 ? true : false;
                    users.Add(user);
                }
                rdr.Close();
            }
            catch (Exception ex)
            {
                Debug.WriteLine("==============");
                Debug.WriteLine(ex.ToString());
                Debug.WriteLine("==============");
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return users;
        }
        public static long Insert(User user)
        {
            long InsertId = 0;
            MySqlConnection conn = new MySqlConnection(con);
            try
            {
                conn.Open();

                string sql = "INSERT INTO gametest.tb_users "
                    + "(username, password, nickname, is_admin) "
                    + "VALUES(@username, @password, @nickname, @is_admin) ";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@username", user.Username);
                cmd.Parameters.AddWithValue("@nickname", user.Nickname);
                cmd.Parameters.AddWithValue("@password", user.Password);
                cmd.Parameters.AddWithValue("@is_admin", user.Is_Admin);
                cmd.ExecuteNonQuery();
                InsertId = cmd.LastInsertedId;
            }
            catch (Exception ex)
            {
                Debug.WriteLine("数据插入异常：" + ex.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return InsertId;
        }
        public static void Update(User user)
        {
            MySqlConnection conn = new MySqlConnection(con);
            try
            {
                conn.Open();

                string sql = "UPDATE gametest.tb_users "
                    + "SET "
                    + "username=@username, "
                    + "password=@password, "
                    + "nickname=@nickname, "
                    + "is_admin=@is_admin "
                    + "WHERE id=@id ";

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@username", user.Username);
                cmd.Parameters.AddWithValue("@nickname", user.Nickname);
                cmd.Parameters.AddWithValue("@password", user.Password);
                cmd.Parameters.AddWithValue("@is_admin", user.Is_Admin);
                cmd.Parameters.AddWithValue("@id", user.Id);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Debug.WriteLine("数据更新异常：" + ex.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
        public static void Delete(long id)
        {
            MySqlConnection conn = new MySqlConnection(con);
            try
            {
                conn.Open();
                string sql = "DELETE FROM gametest.tb_users WHERE id=@id";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Debug.WriteLine("数据删除错误：" + ex.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
    }
}